package com.lee.mysql.util;

import com.lee.mysql.pojo.Column;
import com.lee.mysql.pojo.Index;
import com.lee.mysql.pojo.SourceData;
import com.lee.mysql.pojo.Table;
import org.springframework.util.StringUtils;
import java.util.ArrayList;
import java.util.List;

public class CompareUnits {
    private static final String RGX1 = "[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}";
    private static final String RGX2 = "[0-9]{4}-[0-9]{2}-[0-9]{2}";
    private static final String RGX3 = "[0-9]{2}:[0-9]{2}:[0-9]{2}";

    private SourceData source;
    private SourceData target;

    private List<String> changeSql;

    public CompareUnits(SourceData source, SourceData target) {
        this.source = source;
        this.target = target;
        changeSql = new ArrayList<String>();
    }

    public void compare() {
        //compareSchema();
        compareTables();
        compareKeys();
    }

//    private void compareSchema() {
//        // if not exist
//        // changeSql.add("create database if not exists " + SqlUtil.getDbString(source.getSchema())+";");
//        source.init();
//        target.init();
//    }

    private void compareTables() {
        for (Table table : source.getTables().values()) {
            if (target.getTables().get(table.getTableName()) == null) {
                // 如果对应的target没有这张表,直接把create Table拿出
                changeSql.add(table.getCreateTable()+";");
                continue;
            }
            // 这样就需要比较两者的字段
            compareSingelTable(table, target.getTables().get(table.getTableName()));
        }

    }

    private void compareSingelTable(Table sourceTable, Table targetTable) {
        compareColumns(sourceTable, targetTable);
    }


    /** 替换sql语句中的关键字(未罗列到的自行添加) */
    private String changeKeyWord(String colName){
        if ("index".equalsIgnoreCase(colName)){
            colName = colName.replaceAll("index", "`index`");
        }
        if ("status".equalsIgnoreCase(colName)){
            colName = colName.replaceAll("status", "`status`");
        }
        if ("name".equalsIgnoreCase(colName)){
            colName = colName.replaceAll("name", "`name`");
        }
        if ("type".equalsIgnoreCase(colName)){
            colName = colName.replaceAll("type", "`type`");
        }
        if ("dec".equalsIgnoreCase(colName)){
            colName = colName.replaceAll("dec", "`dec`");
        }
        if ("year".equalsIgnoreCase(colName)){
            colName = colName.replaceAll("year", "`year`");
        }
        if ("read".equalsIgnoreCase(colName)){
            colName = colName.replaceAll("read", "`read`");
        }
        if ("limit".equalsIgnoreCase(colName)){
            colName = colName.replaceAll("limit", "`limit`");
        }

        return colName;
    }

    private void compareColumns(Table sourceTable, Table targetTable) {
        // 记录最后一个比较的column
        String after = null;
        for (Column column : sourceTable.getColumns().values()) {
            if (targetTable.getColumns().get(column.getName()) == null) {
                String colName = column.getName();
                colName = changeKeyWord(colName);

                // 如果对应的target没有这个字段,直接alter
                String sql = "alter table " + target.getDb() + "." + targetTable.getTableName() + " add " + colName + " ";
                sql += column.getType() + " ";
                if ("NO".equals(column.getIsNull())) {
                    sql += "NOT NULL ";
                } else {
                    sql += "NULL ";
                }
                if (column.getDefaultValue() != null) {
                    if ("NULL".equalsIgnoreCase(column.getDefaultValue())){
                        sql += "DEFAULT " + column.getDefaultValue() + " ";
                    }else {
                        sql += "DEFAULT " + SqlUtil.getDbString(column.getDefaultValue()) + " ";
                    }
                }
                if (column.getComment() != null) {
                    sql += "COMMENT " + SqlUtil.getDbString(column.getComment()) + " ";
                }
                if (after != null) {
                    sql += "after " + after;
                }
                changeSql.add(sql+";");
            } else {
                String colName = column.getName();
                colName = changeKeyWord(colName);

                // 检查对应的source 和 target的属性
                String sql =
                        "alter table " + target.getDb() + "." + targetTable.getTableName() + " change " + colName + " ";
                Column sourceColumn = column;
                Column targetColumn = targetTable.getColumns().get(sourceColumn.getName());
                // 比较两者字段,如果返回null,表明一致
                String sqlExtend = compareSingleColumn(sourceColumn, targetColumn);
                if (sqlExtend != null) {
                    changeSql.add(sql + sqlExtend+";");
                }
            }
            after = column.getName();
        }

        // remove the target redundancy columns
        for (Column column : targetTable.getColumns().values()) {
            if (sourceTable.getColumns().get(column.getName()) == null) {
                // redundancy , so drop it
                String sql = "alter table " + target.getDb() + "." + targetTable.getTableName() + " drop " + column
                        .getName() + " ";
                changeSql.add(sql+";");
            }
        }
    }

    private String compareSingleColumn(Column sourceColumn, Column targetColumn) {
        List<String> modify = new ArrayList<String>();
        if (sourceColumn.equals(targetColumn)) {
            return null;
        }
        String changeSql = "";
        String cloName = sourceColumn.getName();
        if (!cloName.equals(targetColumn.getName())) {
            // never reach here
            throw new RuntimeException("the bug in this tool");
        }

        String changeName = changeKeyWord(cloName);

        changeSql += changeName + " ";
        changeSql += sourceColumn.getType() + " ";
        if ("NO".equals(sourceColumn.getIsNull())) {
            changeSql += "NOT NULL ";
        } else {
            changeSql += "NULL ";
        }
        if (sourceColumn.getExtra().toUpperCase().indexOf("AUTO_INCREMENT") != -1) {
            changeSql += "AUTO_INCREMENT ";
        }

        String dfVal = sourceColumn.getDefaultValue();
        if (!StringUtils.isEmpty(dfVal)) {
            if (dfVal.matches(RGX1) || dfVal.matches(RGX2) || dfVal.matches(RGX3)){
                changeSql += "DEFAULT " + SqlUtil.getDbString(dfVal) + " ";
            }else {
                changeSql += "DEFAULT " + dfVal + " ";
            }
        }
        if (sourceColumn.getComment() != null) {
            changeSql += "COMMENT " + SqlUtil.getDbString(sourceColumn.getComment()) + " ";
        }
        return changeSql;
    }

    // compare the index
    private void compareKeys() {
        for (Table table : source.getTables().values()) {
            // 这样就需要比较两者的索引
            if (target.getTables().get(table.getTableName()) != null) {
                compareSingleKeys(table, target.getTables().get(table.getTableName()));
            }
        }
    }

    private void compareSingleKeys(Table sourceTable, Table targetTable) {
        for (Index index : sourceTable.getIndexes().values()) {
            String sql = "alter table " + target.getDb() + "." + targetTable.getTableName() + " ";
            if (targetTable.getIndexes().get(index.getIndexName()) == null) {
                if ("PRIMARY".equals(index.getIndexName())) {
                    sql += "add primary key ";
                } else {
                    if ("0".equals(index.getNotUnique())) {
                        sql += "add unique "+index.getIndexName()+" ";
                    } else {
                        sql += "add index "+index.getIndexName()+" ";
                    }
                }
                sql += "(`";
                for (String key : index.getColumns()) {
                    sql += key.trim() + "`,`";
                }
                // 去掉最后一个,`
                sql = sql.substring(0, sql.length() - 2) + ")";
                changeSql.add(sql+";");
            }
        }
        for (Index index : targetTable.getIndexes().values()) {
            if (sourceTable.getIndexes().get(index.getIndexName()) == null) {
                // 表明此索引多余
                String sql = "alter table " + target.getDb() + "." + targetTable.getTableName() + " ";
                if ("PRIMARY".equals(index.getIndexName())) {
                    sql += "drop primary key ";
                } else {
                    sql += "drop index " + index.getIndexName();
                }
                changeSql.add(sql+";");
            }
        }
    }

    public SourceData getSource() {
        return source;
    }

    public void setSource(SourceData source) {
        this.source = source;
    }

    public SourceData getTarget() {
        return target;
    }

    public void setTarget(SourceData target) {
        this.target = target;
    }

    public List<String> getChangeSql() {
        return changeSql;
    }

    public void setChangeSql(List<String> changeSql) {
        this.changeSql = changeSql;
    }
}
